﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using MyLib;
using System.Configuration;
using Billing2;

public partial class DBCommands2 : MyLib.DAL_MSSQL
{
    public string SP_Subscriber_CheckStartDate
    (
        string AssetID,
        DateTime EffectiveDate,
        DateTime TerminatedDate,
        long IgnoreSubscriberId
    )
    {
        IDbConnection conn = null;
        IDbTransaction tran = null;
        IDbCommand cmd = null;
        IDbDataAdapter da = null;
        try
        {
            conn = this.GetConnection();
            tran = this.GetTransaction();
            cmd = this.CreateDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SP_Subscriber_CheckStartDate";
            cmd.CommandTimeout = this.ExecuteTimeout;
            cmd.CommandType = CommandType.StoredProcedure;
            if (tran != null) cmd.Transaction = tran;

            AddParam(cmd, "@AssetID", DbType.String, 100, 50, 0, ParameterDirection.Input, AssetID);
            AddParam(cmd, "@EffectiveDate", DbType.DateTime, 8, 23, 3, ParameterDirection.Input, EffectiveDate);
            if (TerminatedDate == DateTime.MinValue)
                AddParam(cmd, "@TerminatedDate", DbType.DateTime, 8, 23, 3, ParameterDirection.Input, DBNull.Value);
            else
                AddParam(cmd, "@TerminatedDate", DbType.DateTime, 8, 23, 3, ParameterDirection.Input, TerminatedDate);
            AddParam(cmd, "@IgnoreSubscriberId", DbType.Int32, 8, 19, 0, ParameterDirection.Input, IgnoreSubscriberId);
            AddParam(cmd, "@RETURN_VALUE", DbType.Int32, 4, 4, 0, ParameterDirection.ReturnValue, null);
            da = CreateDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0].Rows[0][0].ToString();
        }
        finally
        {
            if (da != null) da = null;
            if (cmd != null) cmd.Dispose();
            if (conn != null && !this.InTransaction)
            {
                conn.Close();
                conn.Dispose();
            }
        }
    }

    public void SP_DetailsInChangeRequest_Insert2
    (
        int ChangeRequestID,
        int SubscriberID,
        int New_CostCentreID,
        DateTime New_EffectiveDate,
        DateTime New_TerminatedDate,
        string New_Email,
        string New_Username,
        string New_ServiceType,
        string New_AssetID,
        string New_Hostname,
        string New_OS,
        int New_Status,
        ref int xPrimaryKey
    )
    {
        IDbConnection conn = null;
        IDbTransaction tran = null;
        IDbCommand cmd = null;
        try
        {
            conn = this.GetConnection();
            tran = this.GetTransaction();
            cmd = this.CreateDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SP_DetailsInChangeRequest_Insert";
            cmd.CommandTimeout = this.ExecuteTimeout;
            cmd.CommandType = CommandType.StoredProcedure;
            if (tran != null) cmd.Transaction = tran;

            AddParam(cmd, "@ChangeRequestID", DbType.Int64, 8, 19, 0, ParameterDirection.Input, ChangeRequestID);
            AddParam(cmd, "@SubscriberID", DbType.Int64, 8, 19, 0, ParameterDirection.Input, SubscriberID);
            AddParam(cmd, "@New_CostCentreID", DbType.Int64, 8, 19, 0, ParameterDirection.Input, New_CostCentreID);

            if (New_EffectiveDate == DateTime.MinValue)
                AddParam(cmd, "@New_EffectiveDate", DbType.DateTime, 8, 23, 3, ParameterDirection.Input, DBNull.Value);
            else
                AddParam(cmd, "@New_EffectiveDate", DbType.DateTime, 8, 23, 3, ParameterDirection.Input, New_EffectiveDate);

            if (New_TerminatedDate == DateTime.MinValue)
                AddParam(cmd, "@New_TerminatedDate", DbType.DateTime, 8, 23, 3, ParameterDirection.Input, DBNull.Value);
            else
                AddParam(cmd, "@New_TerminatedDate", DbType.DateTime, 8, 23, 3, ParameterDirection.Input, New_TerminatedDate);

            AddParam(cmd, "@New_Email", DbType.String, 400, 200, 0, ParameterDirection.Input, New_Email);
            AddParam(cmd, "@New_Username", DbType.String, 100, 50, 0, ParameterDirection.Input, New_Username);
            AddParam(cmd, "@New_ServiceType", DbType.String, 100, 50, 0, ParameterDirection.Input, New_ServiceType);
            AddParam(cmd, "@New_AssetID", DbType.String, 100, 50, 0, ParameterDirection.Input, New_AssetID);
            AddParam(cmd, "@New_Hostname", DbType.String, 100, 50, 0, ParameterDirection.Input, New_Hostname);
            AddParam(cmd, "@New_OS", DbType.String, 100, 50, 0, ParameterDirection.Input, New_OS);
            AddParam(cmd, "@New_Status", DbType.Int32, 1, 1, 0, ParameterDirection.Input, New_Status);
            AddParam(cmd, "@xPrimaryKey", DbType.Int64, 8, 19, 0, ParameterDirection.Output, null);
            cmd.ExecuteNonQuery();

            xPrimaryKey = get_int_value(((IDbDataParameter)cmd.Parameters["@xPrimaryKey"]).Value);
        }
        finally
        {
            if (cmd != null) cmd.Dispose();
            if (conn != null && !this.InTransaction)
            {
                conn.Close();
                conn.Dispose();
            }
        }
    }

    public static DataTable BillReportData_Diff(long BillReportRepositoryID)
    {
        DBCommands2.TBL_BillReportRepository report = Common.db.BillReportRepository.selectPK2(BillReportRepositoryID);
        DataTable reports = Common.db.Select(@"
            SELECT *
	        FROM BillReportRepository
	        WHERE
		        BillPeriodID = @BillPeriodID 
		        AND BillReportTemplateID = @BillReportTemplateID
		        AND SiteID = @SiteID
		        AND CostCentreID = @CostCentreID
            ORDER BY version desc
            ",
            report.BillPeriodID,
            report.BillReportTemplateID,
            report.SiteID,
            report.CostCentreID);

        if (reports.Rows.Count <= 1)
            return null;

        DataTable diff = null;
        for (int i = 0; i < reports.Rows.Count - 1; i++)
        {
            long new_id = Convert.ToInt64(reports.Rows[i]["ID"]);
            long old_id = Convert.ToInt64(reports.Rows[i + 1]["ID"]);
            DataSet temp = Common.db.sp_BillReportData_select(new_id, old_id);
            if (diff == null)
            {
                diff = temp.Tables[0].Copy();
            }
            else
            {
                foreach (DataRow row in temp.Tables[0].Rows)
                    diff.ImportRow(row);
            }
        }
        return diff;
    }

    public static TBL_BillReportData_1 TBL_BillReportData_1_ReadRow(DataRow row)
    {
        TBL_BillReportData_1 record = new TBL_BillReportData_1();
        record.Version = get_long_value(row["Version"]);
        record.Year0 = get_string_value(row["Year0"]);
        record.Month0 = get_string_value(row["Month0"]);
        record.StartDate = get_DateTime_value(row["StartDate"]);
        record.EndDate = get_DateTime_value(row["EndDate"]);
        record.OrgIDName = get_string_value(row["OrgIDName"]);
        record.SiteIDName = get_string_value(row["SiteIDName"]);
        record.CostCentreIDName = get_string_value(row["CostCentreIDName"]);
        record.ServiceFormID = get_long_value(row["ServiceFormID"]);
        record.OrgID = get_long_value(row["OrgID"]);
        record.SiteID = get_long_value(row["SiteID"]);
        record.CostCentreID = get_long_value(row["CostCentreID"]);
        record.Email = get_string_value(row["Email"]);
        record.EffectiveDate = get_DateTime_value(row["EffectiveDate"]);
        record.TerminatedDate = get_DateTime_value(row["TerminatedDate"]);
        record.UnitPrice = get_decimal_value(row["UnitPrice"]);
        record.OneTimeCharge = get_decimal_value(row["OneTimeCharge"]);
        record.Remarks = get_string_value(row["Remarks"]);
        record.AssetID = get_string_value(row["AssetID"]);
        record.Username = get_string_value(row["Username"]);
        return record;
    }

}
